Imports DTO
Imports System.Data.OleDb
Public Class GiaoVienDAO
    Inherits AbstractDAO
    Public Function LayBang() As DataTable
        Dim dt As New DataTable()
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "select HoTenGiaoVien,TenTat from GiaoVien"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function
    Public Sub CapNhatBang(ByVal dt As DataTable)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Select HoTenGiaoVien,TenTat from GiaoVien"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        Dim cb As New OleDbCommandBuilder(da)
        da.Update(dt)
        cn.Close()
    End Sub
    Public Function LayDanhSach() As IList
        Dim ds As New ArrayList()
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Select * from GiaoVien"
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            Dim gvDTO As New GiaoVienDTO
            gvDTO.MaGiaoVien = dr("MaGiaoVien")
            gvDTO.HoTenGiaoVien = dr("HoTenGiaoVien")
            gvDTO.TenTat = dr("TenTat")
            gvDTO.DiaChi = dr("DiaChi")
            gvDTO.DienThoai = dr("DienThoai")
            ds.Add(gvDTO)
        End While
        dr.Close()
        cn.Close()
        Return ds
    End Function
    Public Function TiemKiemTheoMa(ByVal magiaovien As String) As GiaoVienDTO
        Dim gvDTO As New GiaoVienDTO
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Select * from GiaoVien Where MaGiaoVien = " & magiaovien
        Dim cmd As New OleDbCommand(strSQL, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())

            gvDTO.MaGiaoVien = dr("MaGiaoVien")
            gvDTO.HoTenGiaoVien = dr("HoTenGiaoVien")
            gvDTO.TenTat = dr("TenTat")
            gvDTO.DiaChi = dr("DiaChi")
            gvDTO.DienThoai = dr("DienThoai")

        End While
        dr.Close()
        cn.Close()
        Return gvDTO
    End Function
    Public Sub Them(ByVal gvDTO As GiaoVienDTO)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Insert into GiaoVien(MaGiaoVien, HoTenGiaoVien, TenTat, DiaChi, DienThoai) value= " & _
        " ( '" & gvDTO.MaGiaoVien & "', '" & gvDTO.HoTenGiaoVien & "', '" & gvDTO.TenTat & "', '" & gvDTO.DiaChi & "', " & _
        gvDTO.DienThoai & ")"
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        strSQL = "Select @@INDENTITY"
        cmd = New OleDbCommand(strSQL, cn)
        gvDTO.MaGiaoVien = cmd.ExecuteScalar()
        cn.Close()
    End Sub
    Public Sub XoaTheoMa(ByVal magiaovien As String)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Delete from GiaoVien Where MaGiaoVien= " & magiaovien
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        cn.Close()

    End Sub
    Public Sub Sua(ByVal gvDTO As GiaoVienDTO)
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim strSQL As String
        strSQL = "Update GiaoVien Set " & _
                " HoTenGiaoVien= '" & gvDTO.HoTenGiaoVien & _
                "' and TenTat= '" & gvDTO.TenTat & "' and DiaChi= '" & gvDTO.DiaChi & _
                "' and DienThoai= " & gvDTO.DienThoai & "Where MaGiaoVien= '" & gvDTO.MaGiaoVien & " '"
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub
End Class
